﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;

/// <summary>
/// Summary description for ListAndProcessData
/// </summary>
public class ListAndProcessData
{
	public ListAndProcessData()
	{
	}

    LinQDataDataContext da = new LinQDataDataContext();

    int idBusticket;
    int idCustomer;
    int idEmployee;
    string fullNameCustomer;
    int indentityCard;
    string fullNameEmloyee;
    int busticketAmount;
    int busticketStatus;

    int IdBusCar = 0;
    int idgroup = 0;
    int IDBusType = 0;
    int IdLocation = 0;
    int IdBTD = 1;
    int IDBusTicket = 0;
    int IdCustomer = 0;
    int totalseats = 0;
    string description = null;
    string BusNo = null;
    string SeatsUsed = null;
    string NameType = null;
    string CustomerName = null;
    string StartLocation = null;
    string EndLocation = null;
    string DateStart = null;
    string TimePlan = null;
    int Price = 0;
    string SeatNo = null;
    string Status = null;
    int Amount = 0;

    int idcus = 0, idemployee = 0;
    string cusname = null, employeename = null;
    string groupAccount = null;
    string loginName = null;
    string password = null;
    string sex = null;
    string phone = null;
    string address = null;
    string email = null;
    int age = 0;
    int indetitycard = 0;
    string qualification = null;
    DateTime createDate;

    List<ShowBusTicket> st = new List<ShowBusTicket>();
    List<ShowUpdateGroup> ga = new List<ShowUpdateGroup>();

    public List<ListPlanLocation> StartTravelPlan()
    {
        List<ListPlanLocation> cl = new List<ListPlanLocation>();
        string a1 = null;

        var tp = (from t in da.TravelPlans
                  select new
                  {
                      t.StartLocation
                  }).Distinct();
        
        
        foreach (var a in tp)
        {
            a1 = a.StartLocation.ToString();
            cl.Add(new ListPlanLocation(a1));
        }
        return cl;
    }
    public List<ListPlanLocation> getEndLocation(string slocation)
    {
        var a = from t in da.TravelPlans
                where t.StartLocation == slocation
                select new
                {

                    t.EndLocation,

                };
        List<ListPlanLocation> list = new List<ListPlanLocation>();
        foreach (var item in a)
        {

            EndLocation = item.EndLocation;
            list.Add(new ListPlanLocation(EndLocation));
        }
        return list;
    }
    public List<ListPlanLocation> EndTravelPlan()
    {
        List<ListPlanLocation> lel = new List<ListPlanLocation>();
        string a1 = null;

        var tp = (from t in da.TravelPlans
                  select new
                  {
                      t.EndLocation
                  }).Distinct();
        
        
        foreach (var a in tp)
        {
            a1 = a.EndLocation.ToString();
            lel.Add(new ListPlanLocation(a1));
        }
        return lel;
    }

    public List<StartLocation1> getSLocation()
    {
        var a = (from t in da.TravelPlans

                 select new
                 {

                     t.StartLocation,

                 }).Distinct();
        List<StartLocation1> list = new List<StartLocation1>();
        foreach (var item in a)
        {

            StartLocation = item.StartLocation;
            list.Add(new StartLocation1(StartLocation));
        }
        return list;
    }

    public List<TravelPlan> getAllTravelPlan()
    {
        var TravelPlan = da.TravelPlans.Select(c => c).ToList();
        return TravelPlan;
    }

    public List<Customers> getAllCustomer()
    {
        var a = from t in da.Customers
                select new
                {
                    t.IdCustomer,
                    t.FullName,
                    t.Sex,
                    t.Age,
                    t.Address,
                    t.Email,
                    t.Phone,
                    t.IndentityCard,

                };
        List<Customers> lsit = new List<Customers>();
        foreach (var item in a)
        {
            idcus = item.IdCustomer;
            cusname = item.FullName;
            if (item.Sex == 0)
            {
                sex = "Male";
            }
            else
            {
                sex = "Female";
            }
            phone = item.Phone;
            address = item.Address;
            email = item.Email;
            age = Convert.ToInt32(item.Age);
            indetitycard = Convert.ToInt32(item.IndentityCard);

            lsit.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));
        }
        return lsit;
    }

    public Boolean UpdateCustomer(String id, string fullNameCustomer, String age, int sex, String address, String phone, String email, String identityCard)
    {
        Customer customer = da.Customers.Single(c => c.IdCustomer == int.Parse(id));
        customer.FullName = fullNameCustomer;
        customer.Age = int.Parse(age);
        customer.Sex = sex;
        customer.Address = address;
        customer.Phone = phone;
        customer.Email = email;
        customer.IndentityCard = int.Parse(identityCard);

        da.SubmitChanges();
        return true;

    }

    public List<ListBookPlan> getBookPlan(string startplan,string stopplan,string dateplan)
    {
        var bookticket = (from t in da.BusTypes
                        from t0 in da.BusCars
                        from t1 in da.TravelPlans
                        where
                          t0.IDBusType == t.IDBusType &&
                          t0.IdLocation == t1.IdLocation &&
                          t1.StartLocation == startplan &&
                          t1.EndLocation == stopplan &&
                          t1.DateStart == dateplan
                        select new
                        {
                            t.IDBusType,
                            t0.IdBusCar,
                            t1.IdLocation,
                            
                            t.Name,
                            t.Price,
                            t.TotalSeats,

                            t0.BusNo,
                            t0.Status,
                            t0.SeatsUsed,

                            t1.StartLocation,
                            t1.EndLocation,
                            t1.DateStart,
                            t1.TimePlan
                        }).ToList();
        List<ListBookPlan> lbp = new List<ListBookPlan>();

        foreach (var l in bookticket)
        {
            if (l.Status == 0)
            {
                lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo,l.SeatsUsed, "Available", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));

            }
            else
            {
                lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo,l.SeatsUsed, "Not Available", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));
            }
        }
        return lbp;
    }

    public List<ClassGetBusType> getbusname()
    {
        var busname = (from t in da.BusTypes

                       select new
                       {
                           t.Name,

                       }).Distinct();
        List<ClassGetBusType> list = new List<ClassGetBusType>();
        foreach (var item in busname)
        {
            list.Add(new ClassGetBusType(item.Name));
        }
        return list;
    }

    public List<ListBookPlan> searchBusName(string busname, int status)
    {
        var searchbustype = (from t in da.BusTypes
                             from t0 in da.BusCars
                             from t1 in da.TravelPlans
                             where
                                 t0.IDBusType == t.IDBusType &&
                                 t0.IdLocation == t1.IdLocation &&
                                 t.Name.StartsWith(busname) &&
                                 t0.Status == status
                             select new
                             {
                                 t.IDBusType,
                                 t0.IdBusCar,
                                 t1.IdLocation,

                                 t.Name,
                                 t.Price,
                                 t.TotalSeats,

                                 t0.BusNo,
                                 t0.Status,
                                 t0.SeatsUsed,

                                 t1.StartLocation,
                                 t1.EndLocation,
                                 t1.DateStart,
                                 t1.TimePlan
                             }).ToList();
        List<ListBookPlan> lbp = new List<ListBookPlan>();

        if (busname != "----" && status != 2)
        {
            foreach (var l in searchbustype)
            {
                if (l.Status == 0)
                {
                    lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo, l.SeatsUsed, "Available", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));
                }
                else
                {
                    lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo, l.SeatsUsed, "NotAvailable", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));
                }
            }
        }
        var searchbustype1 = (from t in da.BusTypes
                             from t0 in da.BusCars
                             from t1 in da.TravelPlans
                             where
                                 t0.IDBusType == t.IDBusType &&
                                 t0.IdLocation == t1.IdLocation &&
                                 t.Name.StartsWith(busname)
                             select new
                             {
                                 t.IDBusType,
                                 t0.IdBusCar,
                                 t1.IdLocation,

                                 t.Name,
                                 t.Price,
                                 t.TotalSeats,

                                 t0.BusNo,
                                 t0.Status,
                                 t0.SeatsUsed,

                                 t1.StartLocation,
                                 t1.EndLocation,
                                 t1.DateStart,
                                 t1.TimePlan
                             }).ToList();
        if (busname != "----" && status == 2)
        {
            foreach (var l in searchbustype1)
            {
                if (l.Status == 0)
                {
                    lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo, l.SeatsUsed, "Available", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));
                }
                else
                {
                    lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo, l.SeatsUsed, "NotAvailable", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));
                }
            }
        }
        var searchbustype2 = (from t in da.BusTypes
                              from t0 in da.BusCars
                              from t1 in da.TravelPlans
                              where
                                  t0.IDBusType == t.IDBusType &&
                                  t0.IdLocation == t1.IdLocation &&
                                  t0.Status == status
                              select new
                              {
                                  t.IDBusType,
                                  t0.IdBusCar,
                                  t1.IdLocation,

                                  t.Name,
                                  t.Price,
                                  t.TotalSeats,

                                  t0.BusNo,
                                  t0.Status,
                                  t0.SeatsUsed,

                                  t1.StartLocation,
                                  t1.EndLocation,
                                  t1.DateStart,
                                  t1.TimePlan
                              }).ToList();
        if (busname.Equals("----") && status != 2)
        {
            foreach (var l in searchbustype2)
            {
                if (l.Status == 0)
                {
                    lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo, l.SeatsUsed, "Available", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));
                }
                else
                {
                    lbp.Add(new ListBookPlan(Int32.Parse(l.IDBusType.ToString()), Int32.Parse(l.IdBusCar.ToString()), Int32.Parse(l.IdLocation.ToString()), l.Name, Int32.Parse(l.Price.ToString()), Int32.Parse(l.TotalSeats.ToString()), l.BusNo, l.SeatsUsed, "NotAvailable", l.StartLocation, l.EndLocation, l.DateStart, l.TimePlan));
                }
            }
        }
        return lbp;
    }

    public List<ShowBusTicket> getbusticket()
    {
        var busticket = (from t in da.BusTickets
                         from t1 in da.Customers
                         where
                           t.IdCustomer == t1.IdCustomer
                         select new
                         {
                             t.IDBusTicket,
                             t1.IdCustomer,
                             IdEmployee = (System.Int32?)t.Employee.IdEmployee,
                             t1.FullName,
                             Column1 = t.Employee.FullName,
                             t1.IndentityCard,
                             t.Amount,
                             t.Status
                         });
        foreach (var a in busticket)
        {
            idBusticket = a.IDBusTicket;
            idCustomer = a.IdCustomer;
            idEmployee = Convert.ToInt32(a.IdEmployee);
            fullNameCustomer = a.FullName;
            indentityCard = Convert.ToInt32(a.IndentityCard);
            fullNameEmloyee = a.Column1;
            busticketAmount = Convert.ToInt32(a.Amount);
            busticketStatus = Convert.ToInt32(a.Status);
            if (busticketStatus == 1)
            {
                st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "ok"));
            }
            else
            {
                st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "notok"));
            }

        }
        return st;
    }

    public Boolean UpdateBusticket(int id, int amount, string status)
    {
        bool check = false;
        try
        {
            BusTicket updatebus = da.BusTickets.Single(p => p.IDBusTicket == id);
            updatebus.Amount = amount;
            if (status == "ok")
            {
                updatebus.Status = 1;

            }
            else
            {
                updatebus.Status = 0;
            }
            check = true;

            da.SubmitChanges();
        }
        catch
        {
            check = false;
        }

        return check;
    }

    public Boolean DeleteBusticket(int id)
    {
        bool check = false;

        var b = (from t in da.BusTicketDetails
                where
                  t.IDBusTicket == 3
                select new
                {
                    t.IDBusTicket
                }).ToList();
        foreach(var item in b)
        {           
            DeleteBusTicketDetail(Convert.ToString(item.IDBusTicket));
        }
        if(b.Count == 0)
        {
            try
            {
                var a = da.BusTickets.Where(c => c.IDBusTicket == id).ToList();
                foreach (var del in a)
                {
                    da.BusTickets.DeleteOnSubmit(del);
                }
                check = true;
                da.SubmitChanges();
            }
            catch
            {
                check = false;
            }
        }             
        return check;
    }

    public List<ShowBusTicket> searchBucticket(string customerName, string status)
    {
        int statu = 0;
        if (status == "ok")
        {
            statu = 1;
        }
        else if (status == "----")
        {
            statu = 2;
        }
        else
        {
            statu = 0;
        }
        var search1 = (from t in da.BusTickets
                       from t0 in da.Customers
                       from t1 in da.Employees
                       where
                         t.IdCustomer == t0.IdCustomer &&
                         t.IdEmployee == t1.IdEmployee &&
                         t0.FullName.StartsWith(customerName) &&
                         t.Status == statu
                       select new
                       {
                           t.IDBusTicket,
                           t0.IdCustomer,
                           t1.IdEmployee,
                           t0.FullName,
                           t0.IndentityCard,
                           Column1 = t1.FullName,
                           t.Amount,
                           t.Status
                       }).Distinct();
        var search2 = (from t in da.BusTickets
                       from t0 in da.Customers
                       from t1 in da.Employees
                       where
                         t.IdCustomer == t0.IdCustomer &&
                         t.IdEmployee == t1.IdEmployee &&
                         t0.FullName.StartsWith(customerName)
                       select new
                       {
                           t.IDBusTicket,
                           t0.IdCustomer,
                           t1.IdEmployee,
                           t0.FullName,
                           t0.IndentityCard,
                           Column1 = t1.FullName,
                           t.Amount,
                           t.Status
                       }).Distinct();
        var search3 = (from t in da.BusTickets
                       from t0 in da.Customers
                       from t1 in da.Employees
                       where
                         t.IdCustomer == t0.IdCustomer &&
                         t.IdEmployee == t1.IdEmployee &&
                         t.Status == statu
                       select new
                       {
                           t.IDBusTicket,
                           t0.IdCustomer,
                           t1.IdEmployee,
                           t0.FullName,
                           t0.IndentityCard,
                           Column1 = t1.FullName,
                           t.Amount,
                           t.Status
                       }).Distinct();
        if (customerName.Length > 0 && statu != 2)
        {
            foreach (var a in search1)
            {
                idBusticket = a.IDBusTicket;
                idCustomer = a.IdCustomer;
                idEmployee = Convert.ToInt32(a.IdEmployee);
                fullNameCustomer = a.FullName;
                indentityCard = Convert.ToInt32(a.IndentityCard);
                fullNameEmloyee = a.Column1;
                busticketAmount = Convert.ToInt32(a.Amount);
                busticketStatus = Convert.ToInt32(a.Status);
                if (busticketStatus == 1)
                {
                    st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "ok"));
                }
                else
                {
                    st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "notok"));
                }
            }
        }
        if (customerName.Length > 0 && statu == 2)
        {
            foreach (var a in search2)
            {
                idBusticket = a.IDBusTicket;
                idCustomer = a.IdCustomer;
                idEmployee = Convert.ToInt32(a.IdEmployee);
                fullNameCustomer = a.FullName;
                indentityCard = Convert.ToInt32(a.IndentityCard);
                fullNameEmloyee = a.Column1;
                busticketAmount = Convert.ToInt32(a.Amount);
                busticketStatus = Convert.ToInt32(a.Status);
                if (busticketStatus == 1)
                {
                    st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "ok"));
                }
                else
                {
                    st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "notok"));
                }
            }
        }
        if (customerName.Equals("") && statu != 2)
        {
            foreach (var a in search3)
            {
                idBusticket = a.IDBusTicket;
                idCustomer = a.IdCustomer;
                idEmployee = Convert.ToInt32(a.IdEmployee);
                fullNameCustomer = a.FullName;
                indentityCard = Convert.ToInt32(a.IndentityCard);
                fullNameEmloyee = a.Column1;
                busticketAmount = Convert.ToInt32(a.Amount);
                busticketStatus = Convert.ToInt32(a.Status);
                if (busticketStatus == 1)
                {
                    st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "ok"));
                }
                else
                {
                    st.Add(new ShowBusTicket(idBusticket, idCustomer, idEmployee, fullNameCustomer, indentityCard, fullNameEmloyee, busticketAmount, "notok"));
                }
            }
        }
        return st;
    }

    public List<GroupAccount> showGroupAccount()
    {
        var groupAccount = da.GroupAccounts.Select(c => c).ToList();

        return groupAccount;
    }

    public List<ShowUpdateGroup> loadGroupName()
    {
        string a1 = null;

        var tp = (from t in da.GroupAccounts
                  select new
                  {
                      t.NameGroup
                  }).Distinct();


        foreach (var a in tp)
        {
            a1 = a.NameGroup.ToString();
            ga.Add(new ShowUpdateGroup(a1));
        }
        return ga;
    }

    public Boolean UpdateGroupAccount(int id, string groupname, string description)
    {
        bool check = false;
        try
        {
            if (id != 1)
            {
                GroupAccount update = da.GroupAccounts.Single(p => p.IdGroup == id);
                update.NameGroup = groupname;
                update.Description = description;
                check = true;
            }
            else
            {
                check = false;
            }
            da.SubmitChanges();
        }
        catch
        {
            check = false;
        }

        return check;
    }
    public Boolean insertGroupAccount(String groupname, String description)
    {
        try
        {
            GroupAccount ga = new GroupAccount();
            ga.NameGroup = groupname;
            ga.Description = description;
            da.GroupAccounts.InsertOnSubmit(ga);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }
    }


    public List<TravelPlan> getTravelPlan()
    {
        var TravelPlan = da.TravelPlans.Select(c => c).ToList();
        return TravelPlan;
    }

    public List<BusCar> getBusCar()
    {

        var BusCar = da.BusCars.Select(c => c).ToList();
        return BusCar;
    }


    public List<BusType> getBusType()
    {
        var BusType = da.BusTypes.Select(c => c).ToList();
        return BusType;
    }

    public List<Bustype1> searchBustype(string type, string price)
    {
        int price1 = 0;
        List<Bustype1> lst = new List<Bustype1>();
        if (type.Length > 0 && price.Length == 0)
        {
            var a = from t in da.BusTypes
                    where t.Name == type
                    select new
                    {
                        t.IDBusType,
                        t.Name,
                        t.Price,
                        t.TotalSeats,
                        t.Description
                    };
            foreach (var item in a)
            {
                IDBusType = item.IDBusType;
                NameType = item.Name;
                Price = Convert.ToInt32(item.Price);
                totalseats = Convert.ToInt32(item.TotalSeats);
                description = item.Description;
                lst.Add(new Bustype1(IDBusType, NameType, totalseats, Price, description));
            }
        }
        if (type.Length == 0 && price.Length > 0)
        {
            price1 = int.Parse(price);
            var b = from t in da.BusTypes
                    where t.Price == price1
                    select new
                    {
                        t.IDBusType,
                        t.Name,
                        t.Price,
                        t.TotalSeats,
                        t.Description
                    };
            foreach (var item in b)
            {
                IDBusType = item.IDBusType;
                NameType = item.Name;
                Price = Convert.ToInt32(item.Price);
                totalseats = Convert.ToInt32(item.TotalSeats);
                description = item.Description;
                lst.Add(new Bustype1(IDBusType, NameType, totalseats, Price, description));
            }
        }
        if (type.Length > 0 && price.Length > 0)
        {
            price1 = int.Parse(price);
            var c = from t in da.BusTypes
                    where t.Name.Equals(type) && t.Price == price1
                    select new
                    {
                        t.IDBusType,
                        t.Name,
                        t.Price,
                        t.TotalSeats,
                        t.Description
                    };
            foreach (var item in c)
            {
                IDBusType = item.IDBusType;
                NameType = item.Name;
                Price = Convert.ToInt32(item.Price);
                totalseats = Convert.ToInt32(item.TotalSeats);
                description = item.Description;
                lst.Add(new Bustype1(IDBusType, NameType, totalseats, Price, description));
            }
        }
        return lst;
    }

    public List<BusCarBusTypeTravelPlan> searchbuscar(string busno, string slocation, string elocation)
    {
        List<BusCarBusTypeTravelPlan> lst = new List<BusCarBusTypeTravelPlan>();
        string st;
        if (busno.Length > 0 && slocation.Length == 0 && elocation.Length == 0)
        {
            var a = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,
                        t1.Status
                    };
            foreach (var item in a)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                if (item.Status == 0)
                {
                    st = "Available";
                }
                else
                {
                    st = "UnAvailable";
                }
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));
            }

        }
        if (busno.Length == 0 && slocation.Length > 0 && elocation.Length == 0)
        {
            var b = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t.StartLocation == slocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,
                        t1.Status
                    };
            foreach (var item in b)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                if (item.Status == 0)
                {
                    st = "Available";
                }
                else
                {
                    st = "UnAvailable";
                }
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));

            }
        }
        if (busno.Length == 0 && slocation.Length == 0 && elocation.Length > 0)
        {
            var c = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,
                        t1.Status
                    };
            foreach (var item in c)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                if (item.Status == 0)
                {
                    st = "Available";
                }
                else
                {
                    st = "UnAvailable";
                }
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));
            }
        }
        if (busno.Length > 0 && slocation.Length > 0 && elocation.Length == 0)
        {
            var d = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t.StartLocation == slocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,
                        t1.Status
                    };
            foreach (var item in d)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                if (item.Status == 0)
                {
                    st = "Available";
                }
                else
                {
                    st = "UnAvailable";
                }
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));
            }
        }
        if (busno.Length > 0 && slocation.Length == 0 && elocation.Length > 0)
        {
            var e = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,
                        t1.Status
                    };
            foreach (var item in e)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                if (item.Status == 0)
                {
                    st = "Available";
                }
                else
                {
                    st = "UnAvailable";
                }
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));
            }
        }
        if (busno.Length == 0 && slocation.Length > 0 && elocation.Length > 0)
        {
            var f = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t.StartLocation == slocation && t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,
                        t1.Status
                    };
            foreach (var item in f)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                if (item.Status == 0)
                {
                    st = "Available";
                }
                else
                {
                    st = "UnAvailable";
                }
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));
            }
        }
        if (busno.Length > 0 && slocation.Length > 0 && elocation.Length > 0)
        {
            var g = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t.StartLocation == slocation && t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,
                        t1.Status
                    };
            foreach (var item in g)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                if (item.Status == 0)
                {
                    st = "Available";
                }
                else
                {
                    st = "UnAvailable";
                }
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));

            }
        }
        return lst;
    }

    public List<TravelPlan1> searchtravelplan(string slocation, string elocation, string date)
    {
        List<TravelPlan1> lst = new List<TravelPlan1>();
        if (slocation.Length > 0 && elocation.Length == 0 && date.Length == 0)
        {
            var a = from t in da.TravelPlans
                    where t.StartLocation == slocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in a)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }

        }
        if (slocation.Length == 0 && elocation.Length > 0 && date.Length == 0)
        {
            var b = from t in da.TravelPlans
                    where t.EndLocation == elocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in b)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length == 0 && elocation.Length == 0 && date.Length > 0)
        {
            var c = from t in da.TravelPlans
                    where t.DateStart == date
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in c)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length > 0 && elocation.Length > 0 && date.Length == 0)
        {
            var d = from t in da.TravelPlans
                    where t.StartLocation == slocation && t.EndLocation == elocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in d)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length > 0 && elocation.Length == 0 && date.Length > 0)
        {
            var e = from t in da.TravelPlans
                    where t.StartLocation == slocation && t.DateStart == date
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in e)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length == 0 && elocation.Length > 0 && date.Length > 0)
        {
            var f = from t in da.TravelPlans
                    where t.EndLocation == elocation && t.DateStart == date
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in f)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length > 0 && elocation.Length > 0 && date.Length > 0)
        {
            var g = from t in da.TravelPlans
                    where t.EndLocation == elocation && t.DateStart == date && t.StartLocation == slocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in g)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        return lst;
    }

    public BusTicketDetail getBusTicketDetail(string id)
    {
        int idticket = int.Parse(id);
        var BusTicketDetail = da.BusTicketDetails.SingleOrDefault(c => c.IDBusTicket == idticket);
        return BusTicketDetail;
    }

    public Boolean InsertTravelPlan(string SLocation, string ELocation, string DateStart, string TimePlan)
    {
        try
        {
            TravelPlan travel = new TravelPlan();
            travel.StartLocation = SLocation;
            travel.EndLocation = ELocation;
            travel.DateStart = DateStart;
            travel.TimePlan = TimePlan;
            da.TravelPlans.InsertOnSubmit(travel);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }


    }

    public Boolean UpdateTravelPlan(string id, string SLocation, string ELocation, string DateStart, string TimePlan)
    {

        try
        {
            TravelPlan travel = da.TravelPlans.Single(c => c.IdLocation == int.Parse(id));
            travel.StartLocation = SLocation;
            travel.EndLocation = ELocation;
            travel.DateStart = DateStart;
            travel.TimePlan = TimePlan;

            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }


    }

    public Boolean InsertBusCar(string Busno, string Seatuse, string SLocation, string BusType, string Elocation, string status)
    {
        try
        {
            int st;
            var a = from t in da.TravelPlans
                    from t1 in da.BusTypes
                    where SLocation == t.StartLocation && BusType == t1.Name && t.EndLocation == Elocation
                    select new
                    {
                        t.IdLocation,
                        t1.IDBusType
                    };
            foreach (var item in a)
            {
                IDBusType = item.IDBusType;
                IdLocation = item.IdLocation;
            }
            if (status.Equals("Available"))
            {
                st = 0;
            }
            else
            {
                st = 1;
            }
            BusCar car = new BusCar();
            car.IDBusType = IDBusType;
            car.IdLocation = IdLocation;
            car.BusNo = Busno;
            car.SeatsUsed = "0";
            car.Status = st;
            da.BusCars.InsertOnSubmit(car);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean UpdateBusCar(int id, string Busno, string Seatuse, string SLocation, string BusType, string Elocation, string status)
    {

        try
        {
            int st;
            var a = from t in da.TravelPlans
                    from t1 in da.BusTypes
                    where SLocation == t.StartLocation && BusType == t1.Name && Elocation == t.EndLocation
                    select new
                    {
                        t.IdLocation,
                        t1.IDBusType
                    };
            foreach (var item in a)
            {
                IDBusType = item.IDBusType;
                IdLocation = item.IdLocation;
            }
            if (status.Equals("Available"))
            {
                st = 0;
            }
            else
            {
                st = 1;
            }
            BusCar car = da.BusCars.Single(c => c.IdBusCar == id);
            car.IDBusType = IDBusType;
            car.IdLocation = IdLocation;
            car.BusNo = Busno;
            car.SeatsUsed = Seatuse;
            car.Status = st;
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }



    }

    public Boolean InsertBusType(string Name, string TotalSeat, string Price, string Des)
    {
        try
        {
            BusType t = new BusType();
            t.Name = Name;
            t.TotalSeats = int.Parse(TotalSeat);
            t.Price = int.Parse(Price);
            t.Description = Des;
            da.BusTypes.InsertOnSubmit(t);
            da.SubmitChanges();


            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean UpdateBusType(string id, string Name, string TotalSeat, string Price, string Des)
    {


        try
        {
            BusType t = da.BusTypes.Single(c => c.IDBusType == int.Parse(id));
            t.Name = Name;
            t.TotalSeats = int.Parse(TotalSeat);
            t.Price = int.Parse(Price);
            t.Description = Des;

            da.SubmitChanges();


            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean InsertTicketDetail(int Price, string SeatNo, string cusname, string busno)
    {
        try
        {
            var a = from t1 in da.BusCars
                    from t3 in da.Customers
                    where t3.FullName.Equals(cusname) && t1.BusNo.Equals(busno)
                    select new
                    {
                        t3.IdCustomer,
                        t1.IdBusCar
                    };
            foreach (var item in a)
            {
                IdCustomer = item.IdCustomer;
                IdBusCar = item.IdBusCar;
            };
            var b = from t in da.BusTickets
                    where t.IdCustomer == IdCustomer
                    select new { t.IDBusTicket };
            foreach (var item in b)
            {
                IDBusTicket = item.IDBusTicket;
            }

            BusTicketDetail bt = new BusTicketDetail();
            bt.IdBusCar = IdBusCar;
            bt.IDBusTicket = IDBusTicket;
            bt.IdCustomer = IdCustomer;
            bt.Price = Price;
            bt.SeatNo = SeatNo;
            da.BusTicketDetails.InsertOnSubmit(bt);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean DeleteTravelPlan(int id)
    {

        bool check = false;

        try
        {
            var a = da.TravelPlans.Where(c => c.IdLocation == id).ToList();
            foreach (var del in a)
            {
                var b = da.BusCars.Where(c => c.IdLocation == del.IdLocation).ToList();
                foreach (var item in b)
                {
                    var d = da.BusTicketDetails.Where(c => c.IdBusCar == item.IdBusCar).ToList();
                    foreach (var item1 in d)
                    {
                        da.BusTicketDetails.DeleteOnSubmit(item1);
                    }
                    da.BusCars.DeleteOnSubmit(item);
                }
                da.TravelPlans.DeleteOnSubmit(del);
                da.SubmitChanges();
            }
            check = true;
        }
        catch
        {
            check = false;
        }

        return check;
    }

    public Boolean DeleteBusCar(string id)
    {
        bool check = false;

        try
        {
            var a = da.BusCars.Where(c => c.IdBusCar == int.Parse(id)).ToList();
            foreach (var del in a)
            {
                var b = da.BusTicketDetails.Where(c => c.IdBusCar == del.IdBusCar).ToList();
                foreach (var item in b)
                {
                    da.BusTicketDetails.DeleteOnSubmit(item);
                }
                da.BusCars.DeleteOnSubmit(del);

            }
            da.SubmitChanges();
            check = true;
        }
        catch
        {
            check = false;
        }

        return check;
    }

    public Boolean DeleteBusType(string id)
    {
        bool check = false;
        try
        {

            var a = da.BusTypes.Where(c => c.IDBusType == int.Parse(id)).ToList();
            foreach (var del in a)
            {
                var b = da.BusCars.Where(c => c.IDBusType == del.IDBusType).ToList();
                foreach (var item in b)
                {
                    da.BusCars.DeleteOnSubmit(item);
                }
                da.BusTypes.DeleteOnSubmit(del);

            }
            da.SubmitChanges();
            check = true;
        }
        catch
        {
            check = false;
        }

        return check;
    }


    public Int32 DeleteBusTicketDetail(string id)
    {
        bool check = false;
        int reprice = 0;
        try
        {
            var a = da.BusTicketDetails.Where(c => c.IdBTD == int.Parse(id)).ToList();
            foreach (var del in a)
            {
                var b = da.BusCars.Where(c => c.IdBusCar == del.IdBusCar);
                foreach (var item in b)
                {
                    int startindex;
                    startindex = item.SeatsUsed.IndexOf(del.SeatNo);
                    string seatuesd1 = item.SeatsUsed.Replace(", ", " ");
                    string seatused2 = seatuesd1.Replace(" " + del.SeatNo, "");
                    string seaused3 = seatused2.Replace(" ", ", ");
                    BusCar car = da.BusCars.Single(c => c.IdBusCar == del.IdBusCar);
                    car.SeatsUsed = seaused3;
                    da.SubmitChanges();
                    var e = da.TravelPlans.Where(c => c.IdLocation == item.IdLocation);
                    foreach (var item1 in e)
                    {
                        System.Globalization.DateTimeFormatInfo dtfi = new System.Globalization.DateTimeFormatInfo();
                        dtfi.ShortDatePattern = "MM/dd/yyyy";
                        dtfi.DateSeparator = "/";
                        DateTime datestart = Convert.ToDateTime(item1.DateStart, dtfi);
                        TimeSpan date = DateTime.Now.Subtract(datestart);
                        if (date.Days == 2)
                        {
                            reprice = Convert.ToInt32(del.Price);
                            BusTicket tk = da.BusTickets.Single(c => c.IDBusTicket == del.IDBusTicket);
                            int totalprice = Convert.ToInt32(tk.Amount);
                            tk.Amount = totalprice - reprice;
                            da.SubmitChanges();
                        }
                        if (date.Days == 1)
                        {
                            //trả lại 85%
                            reprice = (Convert.ToInt32(del.Price) * 85) / 100;
                            BusTicket tk = da.BusTickets.Single(c => c.IDBusTicket == del.IDBusTicket);
                            int totalprice = Convert.ToInt32(tk.Amount);
                            tk.Amount = totalprice - reprice;
                            da.SubmitChanges();
                        }
                        if (date.Days == 0)
                        {
                            //tra lai 70%
                            reprice = (Convert.ToInt32(del.Price) * 70) / 100;
                            BusTicket tk = da.BusTickets.Single(c => c.IDBusTicket == del.IDBusTicket);
                            int totalprice = Convert.ToInt32(tk.Amount);
                            tk.Amount = totalprice - reprice;
                            da.SubmitChanges();
                        }

                    }

                }
                da.BusTicketDetails.DeleteOnSubmit(del);
                da.SubmitChanges();
            }
            check = true;
        }
        catch
        {
            check = false;
        }

        return reprice;
    }

    public List<BusCarBusTypeTravelPlan> getAllBuscartypeplan()
    {
        var a = from t in da.TravelPlans
                from t1 in da.BusCars
                from t2 in da.BusTypes
                where t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                select new
                {
                    t1.IdBusCar,
                    t1.IDBusType,
                    t1.IdLocation,
                    t1.SeatsUsed,
                    t1.BusNo,
                    t2.Name,
                    t.StartLocation,
                    t.EndLocation,
                    t.DateStart,
                    t.TimePlan,
                    t1.Status
                };
        List<BusCarBusTypeTravelPlan> list = new List<BusCarBusTypeTravelPlan>();

        foreach (var item in a)
        {
            IdBusCar = item.IdBusCar;
            IDBusType = Convert.ToInt32(item.IDBusType);
            IdLocation = Convert.ToInt32(item.IdLocation);
            BusNo = item.BusNo;
            SeatsUsed = item.SeatsUsed;
            NameType = item.Name;
            StartLocation = item.StartLocation;
            EndLocation = item.EndLocation;
            DateStart = item.DateStart;
            TimePlan = item.TimePlan;
            if (item.Status == 0)
            {
                Status = "Available";
            }
            else
            {
                Status = "Unavailable";
            }
            list.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan, Status));
        }
        return list;
    }

    public List<ShowTicketDetail> getAllShowTicketDetail(int id)

    {
        var a = from t in da.BusTicketDetails
                from t1 in da.BusTickets
                from t2 in da.BusCars
                from t3 in da.Customers
                where id == t.IDBusTicket && t.IdBusCar == t2.IdBusCar && t.IdCustomer == t3.IdCustomer && t.IDBusTicket == t1.IDBusTicket
                select new
                {
                    t.IdBTD,
                    t.IdBusCar,
                    t.IDBusTicket,
                    t.IdCustomer,
                    t.SeatNo,
                    t1.Amount,
                    t1.Status,
                    t.Price,
                    t2.BusNo,
                    t3.FullName,
                };
        List<ShowTicketDetail> list = new List<ShowTicketDetail>();
        foreach (var item in a)
        {
            IdBTD = item.IdBTD;
            IdCustomer = Convert.ToInt32(item.IdCustomer);
            IDBusTicket = Convert.ToInt32(item.IDBusTicket);
            IdBusCar = Convert.ToInt32(item.IdBusCar);
            Price = Convert.ToInt32(item.Price);
            SeatNo = item.SeatNo;
            BusNo = item.BusNo;
            CustomerName = item.FullName;
            if (Convert.ToInt32(item.Status) == 0)
            {
                Status = "";
            }
            else
            {
                Status = "";
            }
            Amount = Convert.ToInt32(item.Amount);
            list.Add(new ShowTicketDetail(IdBTD, IDBusTicket, IdBusCar, IdCustomer, CustomerName, BusNo, SeatNo, Price));
        }
        return list;
    }

    public List<Employee> Login(string user, string pass)
    {
        byte[] mang = System.Text.Encoding.UTF8.GetBytes(pass);

        MD5CryptoServiceProvider my_md5 = new MD5CryptoServiceProvider();
        mang = my_md5.ComputeHash(mang);
        string password1 = null;
        foreach (byte c in mang)
        {
            password1 = c.ToString();
        }
        List<Employee> lst = new List<Employee>();
        var b = da.Employees.SingleOrDefault(a => a.LoginName == user && a.Password == password1);

        lst.Add(b);

        return lst;



    }

    public List<Employees> getAllEmployee()
    {
        var a = from t in da.Employees
                select new
                {
                    t.IdGroup,
                    t.LoginName,
                    t.IdEmployee,
                    t.FullName,
                    t.Sex,
                    t.Age,
                    t.Address,
                    t.Phone,
                    t.Email,
                    t.Qualification,
                    t.Create_Date
                };
        List<Employees> employeeList = new List<Employees>();
        foreach (var item in a)
        {
            if (item.IdGroup == 1)
            {
                groupAccount = "admin";
            }
            if (item.IdGroup == 2)
            {
                groupAccount = "mod";
            }
            idemployee = item.IdEmployee;
            loginName = item.LoginName;
            employeename = item.FullName;
            if (item.Sex == 0)
            {
                sex = "Male";
            }
            else
            {
                sex = "Female";
            }
            address = item.Address;
            phone = item.Phone;
            email = item.Email;
            age = Convert.ToInt32(item.Age);
            qualification = item.Qualification;
            createDate = Convert.ToDateTime(item.Create_Date);


            employeeList.Add(new Employees(groupAccount, idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));
        }
        return employeeList;
    }

    public List<GroupAccount> getAllGroupAccount()
    {
        var groupAccount = da.GroupAccounts.Select(c => c).ToList();
        return groupAccount;
    }

    public Boolean UpdateEmployee(String groupAccount, int id, String loginName, String fullNameEmployee, String age, int sex, String address, String phone, String email, String qualification)
    {
        Employee employee = da.Employees.Single(c => c.IdEmployee == id);
        employee.IdEmployee = Convert.ToInt32(id);
        employee.IdGroup = Convert.ToInt32(groupAccount);
        employee.LoginName = loginName;
        employee.FullName = fullNameEmployee;
        employee.Age = int.Parse(age);
        employee.Sex = sex;
        employee.Address = address;
        employee.Phone = phone;
        employee.Email = email;
        employee.Qualification = qualification;

        da.SubmitChanges();
        return true;
    }

    public Boolean InsertCustomer(string fullNameCustomer, String age, int sex, String address, String phone, String email, String identityCard)
    {
        try
        {
            Customer customer = new Customer();
            customer.FullName = fullNameCustomer;
            customer.Age = int.Parse(age);
            customer.Sex = sex;
            customer.Address = address;
            customer.Phone = phone;
            customer.Email = email;
            customer.IndentityCard = int.Parse(identityCard);

            da.Customers.InsertOnSubmit(customer);

            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }


    public Boolean InsertEmployee(String groupAccount, String loginName, String password, String fullNameEmployee, String age, int sex, String address, String phone, String email, String qualification)
    {
        ListAndProcessData ws = new ListAndProcessData();
        try
        {
            Employee employee = new Employee();
            employee.IdGroup = Convert.ToInt32(groupAccount);
            employee.LoginName = loginName;
            employee.Password = ws.getMD5Password(password);
            employee.FullName = fullNameEmployee;
            employee.Age = int.Parse(age);
            employee.Sex = sex;
            employee.Address = address;
            employee.Phone = phone;
            employee.Email = email;
            employee.Qualification = qualification;
            employee.Create_Date = DateTime.Now;

            da.Employees.InsertOnSubmit(employee);


            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public Boolean DeleteCustomer(int id)
    {
        bool check = false;

        try
        {
            var a = da.Customers.Where(c => c.IdCustomer == id).ToList();
            foreach (var del in a)
            {
                da.Customers.DeleteOnSubmit(del);

            }
            da.SubmitChanges();
            check = true;
        }
        catch
        {
            check = false;
        }
      
        return check;
    }

    public Boolean DeleteEmployee(int id)
    {
        bool check = false;

        try
        {
            var a = da.Employees.Where(c => c.IdEmployee == id).ToList();
            foreach (var del in a)
            {
                da.Employees.DeleteOnSubmit(del);

            }
            check = true;
        }
        catch
        {
            check = false;
        }
        da.SubmitChanges();
        return check;
    }

    public List<Customers> searchCustomer(string customerName, string identitycard)
    {

        var search1 = (from t in da.Customers
                       where
                        t.FullName.StartsWith(customerName) &&
                        t.IndentityCard.ToString().StartsWith(identitycard)
                       select new
                       {
                           t.IdCustomer,
                           t.FullName,
                           t.Age,
                           t.Sex,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.IndentityCard,

                       }).Distinct();
        List<Customers> searchCustomer = new List<Customers>();
        if (customerName.Length > 0 && indetitycard.ToString().Length > 0)
        {
            foreach (var a in search1)
            {
                idcus = a.IdCustomer;
                cusname = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                indetitycard = Convert.ToInt32(a.IndentityCard);

                searchCustomer.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));

            }
        }
        //Search following 2 elements

        var search2 = (from t in da.Customers
                       where
                        t.FullName.StartsWith(customerName)
                       select new
                       {
                           t.IdCustomer,
                           t.FullName,
                           t.Age,
                           t.Sex,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.IndentityCard,

                       }).Distinct();
        if (customerName.Length > 0 && indetitycard.Equals(""))
        {
            foreach (var a in search2)
            {
                idcus = a.IdCustomer;
                cusname = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                indetitycard = Convert.ToInt32(a.IndentityCard);

                searchCustomer.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));

            }
        }
        //Search following customerName

        var search3 = (from t in da.Customers
                       where
                        t.IndentityCard.ToString().StartsWith(identitycard)
                       select new
                       {
                           t.IdCustomer,
                           t.FullName,
                           t.Age,
                           t.Sex,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.IndentityCard,

                       }).Distinct();
        if (identitycard.Length > 0 && customerName.Equals(""))
        {
            foreach (var a in search3)
            {
                idcus = a.IdCustomer;
                cusname = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                indetitycard = Convert.ToInt32(a.IndentityCard);

                searchCustomer.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));

            }
        }

        return searchCustomer;
    }


    public List<Employees> searchEmployee(string employeeName, string email)
    {

        var search1 = (from t in da.Employees
                       where
                        t.FullName.StartsWith(employeeName) &&
                        t.Email.StartsWith(email)
                       select new
                       {
                           t.IdGroup,
                           t.LoginName,
                           t.IdEmployee,
                           t.FullName,
                           t.Sex,
                           t.Age,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.Qualification,
                           t.Create_Date

                       }).Distinct();
        List<Employees> searchEmployee = new List<Employees>();
        if (employeeName.Length > 0 && email.Length > 0)
        {
            foreach (var a in search1)
            {
                if (a.IdGroup == 1)
                {
                    groupAccount = "admin";
                }
                if (a.IdGroup == 2)
                {
                    groupAccount = "mod";
                }
                idemployee = a.IdEmployee;
                loginName = a.LoginName;
                employeename = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                qualification = a.Qualification;
                createDate = Convert.ToDateTime(a.Create_Date);

                searchEmployee.Add(new Employees(groupAccount, idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));

            }
        }
        //Search following 2 elements

        var search2 = (from t in da.Employees
                       where
                        t.FullName.StartsWith(employeeName)
                       select new
                       {
                           t.IdGroup,
                           t.LoginName,
                           t.IdEmployee,
                           t.FullName,
                           t.Sex,
                           t.Age,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.Qualification,
                           t.Create_Date

                       }).Distinct();
        if (employeeName.Length > 0 && email.Equals(""))
        {
            foreach (var a in search2)
            {
                if (a.IdGroup == 1)
                {
                    groupAccount = "admin";
                }
                if (a.IdGroup == 2)
                {
                    groupAccount = "mod";
                }
                idemployee = a.IdEmployee;
                loginName = a.LoginName;
                employeename = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                qualification = a.Qualification;
                createDate = Convert.ToDateTime(a.Create_Date);

                searchEmployee.Add(new Employees(groupAccount, idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));

            }
        }
        //Search following customerName

        var search3 = (from t in da.Employees
                       where
                        t.Email.StartsWith(email)
                       select new
                       {
                           t.IdGroup,
                           t.LoginName,
                           t.IdEmployee,
                           t.FullName,
                           t.Sex,
                           t.Age,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.Qualification,
                           t.Create_Date

                       }).Distinct();
        if (email.Length > 0 && employeeName.Equals(""))
        {
            foreach (var a in search3)
            {
                if (a.IdGroup == 1)
                {
                    groupAccount = "admin";
                }
                if (a.IdGroup == 2)
                {
                    groupAccount = "mod";
                }
                idemployee = a.IdEmployee;
                loginName = a.LoginName;
                employeename = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                qualification = a.Qualification;
                createDate = Convert.ToDateTime(a.Create_Date);

                searchEmployee.Add(new Employees(groupAccount, idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));

            }
        }

        return searchEmployee;
    }

    public Boolean validateCoincisiveLoginName(string coincideLoginName)
    {
        bool isValid = false;
        var a = from t in da.Employees
                select new
                {
                    t.LoginName,
                };
        foreach (var item in a)
        {
            loginName = item.LoginName;

            if (loginName == coincideLoginName)
            {
                return false;
            }
            if (loginName != coincideLoginName)
            {
                return true;
            }
        }

        return isValid;
    }

    public Boolean validateCoincisiveEmail(string coincideEmail)
    {
        bool isValid = false;
        var a = from t in da.Employees
                select new
                {
                    t.Email,
                };
        foreach (var item in a)
        {
            email = item.Email;

            if (email == coincideEmail)
            {
                return false;
            }
            if (email != coincideEmail)
            {
                return true;
            }
        }

        return isValid;
    }

    public String getMD5Password(string password)
    {
        password = "123456";

        string str_md5 = "";
        byte[] mang = System.Text.Encoding.UTF8.GetBytes(password);

        MD5CryptoServiceProvider my_md5 = new MD5CryptoServiceProvider();
        mang = my_md5.ComputeHash(mang);

        foreach (byte b in mang)
        {
            str_md5 += b.ToString("X2");
        }

        return str_md5;
    }

    public Boolean InsertBusticket(int idemploy, int idcus)
    {
        try
        {
            BusTicket bt = new BusTicket();
            bt.IdCustomer = idcus;
            bt.IdEmployee = idemploy;
            bt.Amount = 0;
            bt.Status = 0;
            da.BusTickets.InsertOnSubmit(bt);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }
    }

    public Boolean InsertBusTicketDetail(int idbusticket, int idbuscar, int idcust, int price, string seats)
    {
        try
        {

            BusTicketDetail bt = new BusTicketDetail();
            bt.IDBusTicket = idbusticket;
            bt.IdBusCar = idbuscar;
            bt.IdCustomer = idcust;
            bt.Price = price;
            bt.SeatNo = seats;

            da.BusTicketDetails.InsertOnSubmit(bt);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public List<BusCar> getBusCarWithId(int idbuscar)
    {
        var buscar = da.BusCars.Where(a => a.IdBusCar == idbuscar).ToList();
        return buscar;
    }


    public Boolean UpdateSeatUsedBusCard(int idbuscard, string Seatuse)
    {
        var getseatuse = getBusCarWithId(idbuscard);
        string stringseatused = null;
        foreach (var item in getseatuse)
        {
            stringseatused = item.SeatsUsed;
        }
        try
        {

            BusCar car = da.BusCars.Single(c => c.IdBusCar == idbuscard);
            if (stringseatused == "0")
            {
                car.SeatsUsed = Seatuse;
            }
            else
            {
                car.SeatsUsed = stringseatused + "," + Seatuse;
            }

            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }



    }

    public Boolean UpdatePriceBusTicket(int idbusticket, int price)
    {
        try
        {

            BusTicket car = da.BusTickets.Single(c => c.IDBusTicket == idbusticket);
            car.Amount = price;
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }
    }

    public Boolean UpdateStatusBuscar(int idbuscar, int status)
    {
        try
        {

            BusCar car = da.BusCars.Single(c => c.IdBusCar == idbuscar);
            car.Status = status;
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }
    }

}